<?php
header ( "Content-type:text/html;charset=utf-8" );
// 关闭notice错误
/*
 * error_reporting ( 0 );
 */


include_once 'PHPExcel.php';
require_once '/PHPExcel/Reader/Excel2007.php';

require_once '/PHPExcel/Reader/Excel5.php';

include '/PHPExcel/IOFactory.php';



define ( 'D_BUG', '1' );
$_SGLOBAL = array ();
$_SC = array ();
$_SC ['dbhost'] = '127.0.0.1'; // 服务器地址
$_SC ['dbuser'] = 'root'; // 用户
$_SC ['dbpw'] = 'root'; // 密码
$_SC ['dbcharset'] = 'utf8'; // 字符集
$_SC ['pconnect'] = 0; // 是否持续连接
$_SC ['dbname'] = 'iEnjoy'; // 数据库
$_SC ['charset'] = 'utf-8'; // 页面字符集
                            
// 时间
$mtime = explode ( ' ', microtime () );
$_SGLOBAL ['timestamp'] = $mtime [1];
$_SGLOBAL ['supe_starttime'] = $_SGLOBAL ['timestamp'] + $mtime [0];
// 链接数据库
dbconnect ();

$radionum = $reqnum = $trwupnum = $pbnum = $qbupdatenum = 0;
$radionum1 = $reqnum1 = $trwupnum1 = $pbnum1 = $qbupdatenum1 = 0;

getExcel ();


function getExcel() {
	global $_SGLOBAL, $_SC;

	
	$ExcelArray = array();
			$rnum =  0;
	
				
				$urqt = getExcelQTRadio (  );
				while ( $uvalue = $_SGLOBAL ['db']->fetch_array ( $urqt ) ) {
				
					#dump($uvalue);exit;
					
					$ExcelArray[$rnum]['channelid'] = $uvalue['channelid'];
					
					$ExcelArray[$rnum]['name'] = $uvalue['name'];
					
					$ExcelArray[$rnum]['playurl'] = $uvalue['playurl'];
					$ExcelArray[$rnum]['description'] = $uvalue['description'];
					
					$ExcelArray[$rnum]['type'] = $uvalue['type'];
					$ExcelArray[$rnum]['frequency'] = $uvalue['frequency'];
					
					if($uvalue['frequency']>108){
						$ExcelArray[$rnum]['AMFM'] = 'AM';
					}else{
						$ExcelArray[$rnum]['AMFM'] = 'FM';
					}
					
					
					$ExcelArray[$rnum]['Rtype'] = '';
					$ExcelArray[$rnum]['playbill'] = '';
					$ExcelArray[$rnum]['country'] = $uvalue['region'];
					
					$rnum++;
				
				}
			
		
	
	$name = 'QTExcelALLfile'; // 生成的Excel文件文件名
	

	
	$data = $ExcelArray;
	
	$enTable = array('channelid','name','frequency','country','Rtype','type','AMFM','playurl','description','playbill');
	$cnTable=array('蜻蜓电台ID','电台名称','频率','国家','电台区域等级','电台类型','收听方式','网络链接','电台介绍','节目单');
	setExcel($data,$cnTable,$enTable,'other',20);

}


 function setExcel($data,$txArr,$txArrEn,$excelVersion = "other",$width="auto")
{
	$excelObj = new PHPExcel();
	$excelObj->setActiveSheetIndex(0);
	$objActSheet = $excelObj->getActiveSheet();
	if(count($txArr) != count($txArrEn) && count($txArrEn) != count($data['0']) && !empty($data))
	{
		echo "表头数组错误，请仔细检查！";
		exit();
	}
	/*确定表头宽度，将表头内容添加到excel文件里*/
	foreach($txArr as $key =>$value)
	{
		$objActSheet->setCellValue(numToEn($key)."1",$value);
		/*设置对齐方式*/
		$objActSheet->getStyle(numToEn($key)."1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		/*设置字体加粗*/
		$objActSheet->getStyle(numToEn($key)."1")->getFont()->setBold(true);
		$width == "auto"? $objActSheet->getColumnDimension(numToEn($key))->getAutoSize(true): $objActSheet->getColumnDimension(numToEn($key))->setWidth($width);
	}
	/*将数据添加到excel里*/
	foreach($data as $key=>$value)
	{
		foreach($txArrEn as $k => $val)
		{
			$objActSheet->getStyle(numToEn($k).($key+2))->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
			$objActSheet->setCellValue(numToEn($k).($key+2)," ".$value[$val]);  //在写入Excels单元格的内容之前加一个空格，防止长数字被转化成科学计数法
			$objActSheet->getStyle(numToEn($k).($key+2))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		}
	}
	/*判断生成excel文件版本*/
	$objWriter = "";
	if($excelVersion == "other")
	{
		$objWriter = new PHPExcel_Writer_Excel5($excelObj);
	}
	if($excelVersion == "2007")
	{
		$objWriter = new PHPExcel_Writer_Excel2007($excelObj);
	}
	#$excelName= 'cityToradio.xlsx';
	$excelName= 'QTFreExcelfile.xls';
	$objWriter->save($excelName);
	return $excelName;
}
/**
 根据给定的数字生成至多两位对应EXCEL文件列的字母
 */
 function numToEn($num)
{
	$asc = 0;
	$en = "";
	$num =(int)$num+1;
	if($num<26)                      //判断指定的数字是否需要用两个字母表示
	{
		if((int)$num<10)
		{
			$asc = ord($num);
			$en =chr($asc+16);
		}
		else
		{
			$num_g = substr($num,1,1);
			$num_s = substr($num,0,1);
			$asc = ord($num_g);
			$en =chr($asc+16+10*$num_s);
		}
	}
	else
	{
		$num_complementation = floor($num/26);
		$en_q = numToEn($num_complementation);
		$en_h = $num%26 != 0 ? numToEn($num-$num_complementation*26):"A";
		$en = $en_q.$en_h;
	}
	return $en;
}


function arrayToExcel($data,$name) {
	

	
	$objPHPExcel = new PHPExcel ();
	
	$objPHPExcel->setActiveSheetIndex ( 0 );
	
	$objPHPExcel->getActiveSheet ()->setTitle ( 'firstsheet' );
	
	$objPHPExcel->getDefaultStyle ()->getFont ()->setName ( 'Arial' );
	
	$objPHPExcel->getDefaultStyle ()->getFont ()->setSize ( 10 );
	
	// add data
	
	$i = 2;
	
	foreach ( $data as $line ) {
		
		$objPHPExcel->getActiveSheet ()->setCellValue ( 'A' . $i, $line ['From'] );
		
		$objPHPExcel->getActiveSheet ()->getCell ( 'A' . $i )->setDataType ( 'n' );
		
		$objPHPExcel->getActiveSheet ()->setCellValue ( 'B' . $i, $line ['To'] );
		
		$objPHPExcel->getActiveSheet ()->getCell ( 'B' . $i )->setDataType ( 'n' );
		
		$i ++;
	
	}
	
	$objWriter = PHPExcel_IOFactory::createWriter ( $objPHPExcel, 'Excel5' );
	
	$file = $name.'.xls';
	
	$objWriter->save ( $file );

}

// 数据库连接
function dbconnect() {
	global $_SGLOBAL, $_SC;
	// include_once ('class_mysql.php');
	$_SGLOBAL ['db'] = new dbstuff ();
	$_SGLOBAL ['db']->charset = $_SC ['dbcharset'];
	$_SGLOBAL ['db']->connect ( $_SC ['dbhost'], $_SC ['dbuser'], $_SC ['dbpw'], $_SC ['dbname'], $_SC ['pconnect'] );
}

function dump($arr) {
	echo "<pre>";
	var_dump ( $arr );
	echo "<pre>";
}

function inserttable($tablename, $insertsqlarr, $returnid = 0, $replace = false, $silent = 0) {
	global $_SGLOBAL, $_SC;
	$tablename = $tablename;
	
	$insertkeysql = $insertvaluesql = $comma = '';
	foreach ( $insertsqlarr as $insert_key => $insert_value ) {
		$insertkeysql .= $comma . '`' . $insert_key . '`';
		$insertvaluesql .= $comma . '\'' . $insert_value . '\'';
		$comma = ', ';
	}
	$method = $replace ? 'REPLACE' : 'INSERT';
	$_SGLOBAL ['db']->query ( $method . ' INTO ' . $tablename . ' (' . $insertkeysql . ') VALUES (' . $insertvaluesql . ')', $silent ? 'SILENT' : '' );
	if ($returnid && ! $replace) {
		return $_SGLOBAL ['db']->insert_id ();
	}
}

function updatetable($tablename, $insertsqlarr, $wherearr) {
	global $_SGLOBAL, $_SC;
	$tablename = $tablename;
	
	$insertkeysql = $insertvaluesql = $comma = '';
	foreach ( $insertsqlarr as $insert_key => $insert_value ) {
		$insertkeysql .= $comma . '`' . $insert_key . '` = ' . '\'' . $insert_value . '\'';
		$comma = ', ';
	}
	$wheresql = " where ";
	foreach ( $wherearr as $w_key => $w_value ) {
		if ($w_key == 0) {
			$wheresql = $wheresql . $w_key . "=" . $w_value;
		} else {
			$wheresql = $wheresql . " and " . $w_key . "=" . $w_value;
		}
	}
	
	if ($_SGLOBAL ['db']->query ( 'update ' . $tablename . ' set ' . $insertkeysql . $wheresql )) {
		return 1;
	} else {
		return 0;
	}

}

function getRadioQt($isnull = 0) {
	global $_SGLOBAL, $_SC;
	$andstr = '';
	if ($isnull == 1) {
		$andstr = 'where tr.name="" or tr.name is null';
	} else {
		$andstr = "where  tr.name!='' and tqc.name!=''";
	}
	$sql = 'select 
					tqc.channelid,tqc.rid,tqc.name,tqc.description,tqc.type,tqc.frequency,tr.id,tr.name as rname,tr.channel,tr.description as rdescription,tr.play_url,source 
					from tbl_qt_channels tqc 
					left join tbl_radio tr 
					on tr.name=tqc.name ' . $andstr; // " limit 10";
	
	$query_offica1 = $_SGLOBAL ['db']->query ( $sql );
	
	return $query_offica1;
	// eturn $_SGLOBAL['db']->fetch_array($query_offica1);
}

function getRadioProgram($channelid = 0) {
	global $_SGLOBAL;
	
	$sql = 'select * from tbl_qt_channel_programs where channelid=' . $channelid;
	
	$query_offica1 = $_SGLOBAL ['db']->query ( $sql );
	
	return $query_offica1;
	// eturn $_SGLOBAL['db']->fetch_array($query_offica1);
}

function getRadioFreq($channelid = 0) {
	global $_SGLOBAL;
	
	$sql = 'select * from tbl_radio_freq where radio_id=' . $channelid;
	
	$query_offica1 = $_SGLOBAL ['db']->query ( $sql );
	
	return $query_offica1;
	// eturn $_SGLOBAL['db']->fetch_array($query_offica1);
}

function getPlayBill($channelid = 0, $starttime) {
	global $_SGLOBAL;
	
	$sql = 'select * from tbl_playbill where radio_id=' . $channelid . " and start_time='" . $starttime . "'";
	
	$query_offica1 = $_SGLOBAL ['db']->query ( $sql );
	
	return $query_offica1;
	// eturn $_SGLOBAL['db']->fetch_array($query_offica1);
}

function getExcelQTRadio() {
	global $_SGLOBAL;
	
	$sql = "select tqc.channelid,tqc.name,tqc.description,tqc.type,tqc.frequency,tqcp.playurl,tqc.region from tbl_qt_channels tqc left join tbl_qt_channel_programs tqcp on tqc.channelid=tqcp.channelid  where tqc.rid=96 group by tqc.channelid";
	
	$query_offica1 = $_SGLOBAL ['db']->query ( $sql );
	
	return $query_offica1;
	// eturn $_SGLOBAL['db']->fetch_array($query_offica1);
}

function getPlayBillCount($rid) {
	global $_SGLOBAL;
	
	$sql = "select count(*) count from tbl_playbill where radio_id=" . $rid;
	
	$query_offica1 = $_SGLOBAL ['db']->query ( $sql );
	
	return $query_offica1;
}

function getwbPlayBillCount($rid) {
	global $_SGLOBAL;
	
	$sql = "select count(*) count from tbl_playbill tp left join tbl_radio tr on tp.radio_id = tr.id where tp.radio_id=" . $rid . " and play_url!=''";
	
	$query_offica1 = $_SGLOBAL ['db']->query ( $sql );
	
	return $query_offica1;
}

class dbstuff {
	var $querynum = 0;
	var $link;
	var $charset;
	function connect($dbhost, $dbuser, $dbpw, $dbname = '', $pconnect = 0, $halt = TRUE) {
		if ($pconnect) {
			if (! $this->link = @mysql_pconnect ( $dbhost, $dbuser, $dbpw )) {
				$halt && $this->halt ( 'Can not connect to MySQL server' );
			}
		} else {
			if (! $this->link = @mysql_connect ( $dbhost, $dbuser, $dbpw, 1 )) {
				$halt && $this->halt ( 'Can not connect to MySQL server' );
			}
		}
		
		if ($this->version () > '4.1') {
			if ($this->charset) {
				@mysql_query ( "SET character_set_connection=$this->charset, character_set_results=$this->charset, character_set_client=binary", $this->link );
			}
			if ($this->version () > '5.0.1') {
				@mysql_query ( "SET sql_mode=''", $this->link );
			}
		}
		if ($dbname) {
			@mysql_select_db ( $dbname, $this->link );
		}
	}
	
	function select_db($dbname) {
		return mysql_select_db ( $dbname, $this->link );
	}
	
	function fetch_array($query, $result_type = MYSQL_ASSOC) {
		return mysql_fetch_array ( $query, $result_type );
	}
	
	function query($sql, $type = '') {
		if (D_BUG) {
			global $_SGLOBAL;
			$sqlstarttime = $sqlendttime = 0;
			$mtime = explode ( ' ', microtime () );
			$sqlstarttime = number_format ( ($mtime [1] + $mtime [0] - $_SGLOBAL ['supe_starttime']), 6 ) * 1000;
		}
		$func = $type == 'UNBUFFERED' && @function_exists ( 'mysql_unbuffered_query' ) ? 'mysql_unbuffered_query' : 'mysql_query';
		if (! ($query = $func ( $sql, $this->link )) && $type != 'SILENT') {
			$this->halt ( 'MySQL Query Error', $sql );
		}
		if (D_BUG) {
			$mtime = explode ( ' ', microtime () );
			$sqlendttime = number_format ( ($mtime [1] + $mtime [0] - $_SGLOBAL ['supe_starttime']), 6 ) * 1000;
			$sqltime = round ( ($sqlendttime - $sqlstarttime), 3 );
			
			$explain = array ();
			$info = mysql_info ();
			if ($query && preg_match ( "/^(select )/i", $sql )) {
				$explain = mysql_fetch_assoc ( mysql_query ( 'EXPLAIN ' . $sql, $this->link ) );
			}
			$_SGLOBAL ['debug_query'] [] = array ('sql' => $sql, 'time' => $sqltime, 'info' => $info, 'explain' => $explain );
		}
		$this->querynum ++;
		return $query;
	}
	
	function affected_rows() {
		return mysql_affected_rows ( $this->link );
	}
	
	function error() {
		return (($this->link) ? mysql_error ( $this->link ) : mysql_error ());
	}
	
	function errno() {
		return intval ( ($this->link) ? mysql_errno ( $this->link ) : mysql_errno () );
	}
	
	function result($query, $row) {
		$query = @mysql_result ( $query, $row );
		return $query;
	}
	
	function num_rows($query) {
		$query = mysql_num_rows ( $query );
		return $query;
	}
	
	function num_fields($query) {
		return mysql_num_fields ( $query );
	}
	
	function free_result($query) {
		return mysql_free_result ( $query );
	}
	
	function insert_id() {
		return ($id = mysql_insert_id ( $this->link )) >= 0 ? $id : $this->result ( $this->query ( "SELECT last_insert_id()" ), 0 );
	}
	
	function fetch_row($query) {
		$query = mysql_fetch_row ( $query );
		return $query;
	}
	
	function fetch_fields($query) {
		return mysql_fetch_field ( $query );
	}
	
	function version() {
		return mysql_get_server_info ( $this->link );
	}
	
	function close() {
		return mysql_close ( $this->link );
	}
	
	function halt($message = '', $sql = '') {
		$dberror = $this->error ();
		$dberrno = $this->errno ();
		$help_link = "http://faq.comsenz.com/?type=mysql&dberrno=" . rawurlencode ( $dberrno ) . "&dberror=" . rawurlencode ( $dberror );
		echo "<div style=\"position:absolute;font-size:11px;font-family:verdana,arial;background:#EBEBEB;padding:0.5em;\">
		<b>MySQL Error</b><br>
		<b>Message</b>: $message<br>
		<b>SQL</b>: $sql<br>
		<b>Error</b>: $dberror<br>
		<b>Errno.</b>: $dberrno<br>
		<a href=\"$help_link\" target=\"_blank\">Click here to seek help.</a>
		</div>";
		exit ();
	}
}

